﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Web.Configuration;
using System.Data.SqlClient;
using System.Data;

namespace TraCuuDiemNhom3
{
  public class TienIchSQL
  {
    public static string LayChuoiKetNoi()
    {
        return WebConfigurationManager.ConnectionStrings["dtTracuuDiem"].ConnectionString;
    }

    public static int ChayLenhCapNhatDuLieu(string lenhSQL)
    {
      using (SqlConnection cnn = new SqlConnection())
      {
        cnn.ConnectionString = LayChuoiKetNoi();
        cnn.Open(); //Mo ket noi
        SqlCommand cmd = cnn.CreateCommand(); //Tao doi tuong Command
        cmd.CommandText = lenhSQL;
        return cmd.ExecuteNonQuery();
      }
    }
    public static int ChayLenhCapNhatDuLieu(string lenhSQL,
                                              string[] arrTenThamSo,
                                              object[] arrGiaTriThamSo)
    {
      using (SqlConnection cnn = new SqlConnection())
      {
        cnn.ConnectionString = LayChuoiKetNoi();
        cnn.Open(); //Mo ket noi
        SqlCommand cmd = cnn.CreateCommand(); //Tao doi tuong Command
        cmd.CommandText = lenhSQL;
        //Truyen tham so cho command
        for (int i = 0; i < arrTenThamSo.Length; i++)
        {
          cmd.Parameters.AddWithValue(arrTenThamSo[i], arrGiaTriThamSo[i]);
        }
        return cmd.ExecuteNonQuery();
      }
    }
    
    public static int ChayLenhUpdateDuLieu(string lenhSql,
                                                params object[] thamSo)
    {
      using (SqlConnection cnn = new SqlConnection())
      {
        cnn.ConnectionString = LayChuoiKetNoi();
        cnn.Open(); //Mo ket noi
        SqlCommand cmd = cnn.CreateCommand(); //Tao doi tuong Command
        cmd.CommandText = lenhSql;
        //Truyen tham so cho command
        for (int i = 0; i < thamSo.Length - 1; i+=2)
        {            
            SqlParameter para = new SqlParameter(thamSo[i].ToString(), thamSo[i + 1]);
                   
            cmd.Parameters.Add(para);
        }
        return cmd.ExecuteNonQuery();
      }
    }
    public static DataTable ChayLenhSelectDuLieu(string lenhSqlSelect,
                                                  string[] arrTenThamSo,
                                                object[] arrGiaTriThamSo)
    {
      using (SqlConnection cnn = new SqlConnection())
      {
        cnn.ConnectionString = LayChuoiKetNoi();
        cnn.Open(); //Mo ket noi
        SqlCommand cmd = cnn.CreateCommand(); //Tao doi tuong Command
        cmd.CommandText = lenhSqlSelect;
        //Truyen tham so cho command
        for (int i = 0; i < arrTenThamSo.Length; i++)
        {
          cmd.Parameters.AddWithValue(arrTenThamSo[i], arrGiaTriThamSo[i]);
        }
        SqlDataReader reader = cmd.ExecuteReader();
        DataTable tblKetQua = new DataTable();
        tblKetQua.Load(reader);
        return tblKetQua;
      }
    }

    /// <summary>
    /// Chay cau lenh select
    /// </summary>
    /// <param name="lenhSqlSelect"></param>
    /// <param name="thamSo">"@TenThamSo", giaTri</param>
    /// <returns></returns>
    //Vi du:  ChayLenhSelectDuLieu( sqlSelect, "@Username",txtUsername.Text, "@Passowrd", txtPassword.Text );
    public static DataTable ChayLenhSelectDuLieu(string lenhSqlSelect,                                                  
                                                params object[] thamSo)
    {
      using (SqlConnection cnn = new SqlConnection())
      {
        cnn.ConnectionString = LayChuoiKetNoi();
        cnn.Open(); //Mo ket noi
        SqlCommand cmd = cnn.CreateCommand(); //Tao doi tuong Command
        cmd.CommandText = lenhSqlSelect;
        //Truyen tham so cho command
        for (int i = 0; i < thamSo.Length-1; i+=2)
        {
          cmd.Parameters.AddWithValue(thamSo[i].ToString(), thamSo[i + 1]);
        }
        SqlDataReader reader = cmd.ExecuteReader();
        DataTable tblKetQua = new DataTable();
        tblKetQua.Load(reader);
        return tblKetQua;
      }
    }
    
  }
}